rsubmit;
data delist; set crsp.mse
(keep = cusip date event dlret dlstcd);
where event="DELIST" and dlret<10 and 1961<year(date)<2018;
year=year(date); month=month(date); drop date;
data crsp; set crsp.msf
(keep = cusip date ret prc shrout);
where 1961<year(date)<2018;
year=year(date); month=month(date);
me=abs(prc)*shrout; price=abs(prc);
drop date prc shrout;
proc sort data=delist; by cusip year month;
proc sort data=crsp; by cusip year month;
data crsp; merge crsp (in=in1) delist (in=in2);
by cusip year month; if in1;
proc means; run;

data exch; set crsp.mse (keep = date cusip exchcd shrcd);
year=year(date); month=month(date); drop date;
proc sort data=exch; by cusip year month;
data crsp; merge crsp exch; by cusip year month;
data temp.crsp; set crsp; by cusip year month;
i=0; do while(i<2); lexchcd = lag(exchcd); lshrcd=lag(shrcd);
if cusip = lag(cusip) and exchcd = . then exchcd = lexchcd;
if cusip = lag(cusip) and shrcd = . then shrcd = lshrcd;
i+1; end; if last.month then output;
drop i lexchcd lshrcd;
proc sort data=temp.crsp nodupkey;
by cusip year month;
data temp.crsp; set temp.crsp;
if (nmiss(dlret)=1 or dlret=0) and 500<=dlstcd<600 and (exchcd ne 0 
and exchcd ne 3 and exchcd ne 33) then dlret=-0.3;
if (nmiss(dlret)=1 or dlret=0) and 500<=dlstcd<600 and
(exchcd=0 or exchcd=3 or exchcd=33) then dlret=-0.55;
if nmiss(ret)=1 and nmiss(dlret)=0 then return=dlret*100;
else return=ret*100; if shrcd ne 10 and shrcd ne 11 then delete;
drop event ret dlret dlstcd shrcd;
proc means; run;

rsubmit;
data temp.daily; set crsp.dsf (keep = cusip date ret);
where 1962<year(date)<2018; proc sort; by date; run;
data index; set ff.factors_daily
(keep = date mktrf rf smb hml); proc sort; by date;
data temp.daily; merge temp.daily (in=in1) index;
by date; if in1; eret=(1+ret)/(1+rf)-1;
yearm=(year(date)-1963)*12+month(date);
proc sort; by cusip yearm; run;
proc means data=temp.daily; run;

rsubmit;
options nosource nonotes errors=0;
proc reg data=temp.daily noprint;
model eret = mktrf smb hml / edf;
by cusip yearm;	output out=temp.best
r=residual; proc means; run;

rsubmit;
options source notes errors=5;
data temp.best; set temp.best
(keep = cusip yearm residual);
proc sort; by cusip yearm;
proc means data=temp.best noprint;
var residual; by cusip yearm; 
output out=temp.ivol std(residual)=ivol;
data temp.ivol; set temp.ivol;
year=floor((yearm-0.01)/12)+1963;
month=yearm-(year-1963)*12;
if _freq_<15 then delete; freq=_freq_;
drop _freq_ _type_ yearm; proc means; run;

rsubmit;
proc sort data=temp.crsp; by cusip year month;
proc sort data=temp.ivol; by cusip year month;
data ivol; merge temp.crsp temp.ivol;
by cusip year month; proc sort; by year month;
proc univariate data=ivol noprint;
var ivol; by year month; where exchcd=1;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  pdecile=1;
  IF ivol > DEC20 THEN pdecile=2;
  IF ivol > DEC40 THEN pdecile=3;
  IF ivol > DEC60 THEN pdecile=4;
  IF ivol > DEC80 THEN pdecile=5;
  if nmiss(ivol) then pdecile=.;
drop dec20 dec40 dec60 dec80; proc means; run;
proc sort data=ivol; by cusip year month;
data ivol; set ivol; lme=lag(me);
lcusip=lag(cusip); ivoldecile=lag(pdecile);
if lcusip ne cusip then ivoldecile=.;
if lcusip ne cusip then lme=.;
if year=1963 and 1<month<7 then delete;
proc sort data=ivol nodupkey;
by cusip year month ivol;
proc tabulate data=ivol format=5.3;
class ivoldecile; var return;
table return, (ivoldecile all)*mean; run;

rsubmit;
proc sort data=ivol; by year month ivoldecile;
%macro a(many); %do i=1 %to 5;
proc means data=ivol noprint;
var return; by year month; weight lme;
where ivoldecile=&i; output out=factor&i
mean(return) = port&i; run;
%end; %mend a; %a(1);
data temp.factor; merge factor1 factor2 factor3 factor4 factor5;
by year month; if nmiss(month) then delete;
if (year=1963 and 1<month<7) or year>2017 then delete;
drop _freq_ _type_; proc means data=temp.factor; run;
proc sort; by year month;
proc download data=temp.factor
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\2018.xls"
DBMS=EXCEL REPLACE; SHEET="vw"; RUN;
rsubmit;
proc sort data=ivol; by year month ivoldecile;
%macro a(many); %do i=1 %to 5;
proc means data=ivol noprint;
var return; by year month;
where ivoldecile=&i; output out=factor&i
mean(return) = port&i; run;
%end; %mend a; %a(1);
data temp.factor; merge factor1 factor2 factor3 factor4 factor5;
by year month; if nmiss(month) then delete;
if (year=1963 and 1<month<7) or year>2017 then delete;
drop _freq_ _type_; proc means data=temp.factor; run;
proc sort; by year month;
proc download data=temp.factor
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\2018.xls"
DBMS=EXCEL REPLACE; SHEET="ew"; RUN;

rsubmit;
proc sort data=temp.crsp; by cusip year month;
proc sort data=temp.ivol; by cusip year month;
data ivol; merge temp.crsp temp.ivol;
by cusip year month; proc sort; by year month;
proc univariate data=ivol noprint;
var ivol; by year month; where exchcd=1 and price ge 5;
output out=decile pctlpts = 20 to 80 by 20 pctlpre=dec;
DATA ivol; MERGE ivol decile; BY year month;
  pdecile=1;
  IF ivol > DEC20 THEN pdecile=2;
  IF ivol > DEC40 THEN pdecile=3;
  IF ivol > DEC60 THEN pdecile=4;
  IF ivol > DEC80 THEN pdecile=5;
  if nmiss(ivol) or price<5 then pdecile=.;
drop dec20 dec40 dec60 dec80; proc means; run;
proc sort data=ivol; by cusip year month;
data ivol; set ivol; lme=lag(me);
lcusip=lag(cusip); ivoldecile=lag(pdecile);
if lcusip ne cusip then ivoldecile=.;
if lcusip ne cusip then lme=.;
if year=1963 and 1<month<7 then delete;
proc sort data=ivol nodupkey;
by cusip year month ivol;
proc tabulate data=ivol format=5.3;
class ivoldecile; var return;
table return, (ivoldecile all)*mean; run;

rsubmit;
proc sort data=ivol; by year month ivoldecile;
%macro a(many); %do i=1 %to 5;
proc means data=ivol noprint;
var return; by year month; weight lme;
where ivoldecile=&i; output out=factor&i
mean(return) = port&i; run;
%end; %mend a; %a(1);
data temp.factor; merge factor1 factor2 factor3 factor4 factor5;
by year month; if nmiss(month) then delete;
if (year=1963 and 1<month<7) or year>2017 then delete;
drop _freq_ _type_; proc means data=temp.factor; run;
proc sort; by year month;
proc download data=temp.factor
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\2018.xls"
DBMS=EXCEL REPLACE; SHEET="vw5"; RUN;
rsubmit;
proc sort data=ivol; by year month ivoldecile;
%macro a(many); %do i=1 %to 5;
proc means data=ivol noprint;
var return; by year month;
where ivoldecile=&i; output out=factor&i
mean(return) = port&i; run;
%end; %mend a; %a(1);
data temp.factor; merge factor1 factor2 factor3 factor4 factor5;
by year month; if nmiss(month) then delete;
if (year=1963 and 1<month<7) or year>2017 then delete;
drop _freq_ _type_; proc means data=temp.factor; run;
proc sort; by year month;
proc download data=temp.factor
out=sasuser.factor25 (replace=yes); run;
endrsubmit;
PROC EXPORT DATA= SASUSER.FACTOR25 
OUTFILE= "C:\Sasha\Idiosyncratic Volatility\2018.xls"
DBMS=EXCEL REPLACE; SHEET="ew5"; RUN;
